package com.xinchuang.service.impl;

import cn.hutool.core.bean.BeanUtil;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.excel.write.metadata.fill.FillConfig;
import com.alibaba.fastjson.JSON;
import com.xinchuang.comment.KeyWord;
import com.xinchuang.comment.R;
import com.xinchuang.config.CustomTemplateSheetStrategy;
import com.xinchuang.domain.vo.byd.*;
import com.xinchuang.service.ExportBYDOutQuotationService;
import com.xinchuang.service.IMoldSelectionService;
import com.xinchuang.utils.BigdecimalUtil;
import com.xinchuang.utils.DateUtils;
import com.xinchuang.utils.EasyExcelUtil;
import lombok.RequiredArgsConstructor;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.RegionUtil;
import org.apache.poi.util.IOUtils;
import org.apache.poi.util.Units;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFFont;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.InputStream;
import java.math.BigDecimal;
import java.math.RoundingMode;
import java.util.*;
import java.util.stream.Collectors;
import java.util.stream.IntStream;

@Slf4j
@Service
@RequiredArgsConstructor
public class ExportBYDOutQuotationServiceImpl implements ExportBYDOutQuotationService {

    private final KeyWord keyWord;

    @Autowired
    private IMoldSelectionService iMoldSelectionService;

    @Override
    public String exportBYDOutQuotation(String data) {
        String rootUrl = keyWord.getFileUrl();
        String resultPath = "";
        try {
            R r = JSON.parseObject(data, R.class);
            BYDQuotationVo bydQuotationVo = JSON.parseObject(JSON.toJSONString(r.getData()), BYDQuotationVo.class);
            String moldSelectionId = bydQuotationVo.getMoldSelectionId();
            String projectName = iMoldSelectionService.getProjectName(moldSelectionId);
            resultPath = "file" + File.separator + projectName + "项目-BYD塑胶模具报价单-" + DateUtils.dateTimeNow("yyyyMMdd-SSS") + ".xlsx";
            String filePath = rootUrl + File.separator + resultPath;
            if (Objects.nonNull(bydQuotationVo)) {
                //处理数据
                Map<String, Object> resultMap = new HashMap<>();
                resultMap.put("projectName", bydQuotationVo.getProjectName());
                resultMap.put("inspectionToolFeeTotal", BigdecimalUtil.doubleTo2DecimalString(bydQuotationVo.getInspectionToolFeeTotal()));
                resultMap.put("modlueFeeTotal", BigdecimalUtil.doubleTo2DecimalString(bydQuotationVo.getModlueFeeTotal()));
                BigDecimal samplePieceFeeTotal = bydQuotationVo.getDataList().stream().map(x -> BigDecimal.valueOf(x.getSamplePieceFee())).reduce(BigDecimal.ZERO, BigDecimal::add);
                resultMap.put("samplePieceFeeTotal", BigdecimalUtil.bigDecimalTo2DecimalString(samplePieceFeeTotal));

                List<Map<String, Object>> dataList = new ArrayList<>(bydQuotationVo.getDataList().size());
                List<String> sheetNameList = new ArrayList<>(bydQuotationVo.getDataList().size());
                if (Objects.nonNull(bydQuotationVo.getDataList()) && !bydQuotationVo.getDataList().isEmpty()) {
                    IntStream.range(0, bydQuotationVo.getDataList().size()).forEach(i -> {
                        BYDQuotationDetailVo bydQuotationDetailVo = bydQuotationVo.getDataList().get(i);
                        Map<String, Object> bydQuotationDetailVoMap = BeanUtil.beanToMap(bydQuotationDetailVo, false, false);
                        //序号
                        bydQuotationDetailVoMap.put("index", i + 1);
                        //图片
                        final String imageOfProduct = bydQuotationDetailVo.getImageOfProduct();
                        if (StringUtils.isNotEmpty(imageOfProduct)) {
                            bydQuotationDetailVoMap.put("imageFile", new File(rootUrl + File.separator + imageOfProduct));
                        }
                        bydQuotationDetailVoMap.put("samplePieceFee", BigdecimalUtil.doubleTo2DecimalString(bydQuotationDetailVo.getSamplePieceFee()));
                        bydQuotationDetailVoMap.put("inspectionToolFee", BigdecimalUtil.doubleTo2DecimalString(bydQuotationDetailVo.getInspectionToolFee()));
                        bydQuotationDetailVoMap.put("modlueFee", BigdecimalUtil.doubleTo2DecimalString(bydQuotationDetailVo.getModlueFee()));
                        dataList.add(bydQuotationDetailVoMap);
                        //sheet页名称
                        sheetNameList.add("报价明细" + (i + 1));
                        //样件单价
                        samplePieceFeeTotal.add(BigDecimal.valueOf(bydQuotationDetailVo.getSamplePieceFee())).setScale(2, RoundingMode.HALF_UP);
                    });
                }


                try (ExcelWriter excelWriter = EasyExcel.write(filePath)
                        .inMemory(Boolean.TRUE)
                        .registerWriteHandler(EasyExcelUtil.getStyleStrategy())
                        .registerWriteHandler(new CustomTemplateSheetStrategy(0, bydQuotationVo.getProjectName() + "项目-总报价单"))
                        .withTemplate(getClass().getClassLoader().getResourceAsStream("templates/byd_quotation_template.xlsx"))
                        .build()) {
                    FillConfig fillConfig = FillConfig.builder().forceNewRow(Boolean.TRUE).build();
                    WriteSheet writeSheet = EasyExcel.writerSheet(0).build();
                    excelWriter.fill(dataList, fillConfig, writeSheet);
                    excelWriter.fill(resultMap, writeSheet);
                    excelWriter.finish();
                }

                //在内存中创建一个Excel文件对象
                FileInputStream fileInputStream = new FileInputStream(filePath);
                XSSFWorkbook workbook = new XSSFWorkbook(fileInputStream);

                IntStream.range(0, sheetNameList.size()).forEach(i -> {
                    XSSFSheet sheet = workbook.createSheet(sheetNameList.get(i));
                    BYDQuotationSubDetailVo detailData = bydQuotationVo.getDataList().get(i).getDetailData();

                    List<String> fontBoldList = new ArrayList<>();
                    mergedRegion(sheet, 0, 0, 0, 11, "塑胶模具报价单");
                    fontBoldList.add(0 + "-" + 0);

                    mergedRegion(sheet, 1, 5, 0, 0, "基本信息");
                    fontBoldList.add(1 + "-" + 0);

                    mergedRegion(sheet, 1, 1, 1, 2, "零件编号");
                    mergedRegion(sheet, 2, 2, 1, 2, "零件名称");
                    mergedRegion(sheet, 3, 3, 1, 2, "零件尺寸");
                    mergedRegion(sheet, 4, 4, 1, 2, "零件重量(g)");
                    mergedRegion(sheet, 5, 5, 1, 2, "模具量纲");
                    fontBoldList.add(1 + "-" + 1);
                    fontBoldList.add(2 + "-" + 1);
                    fontBoldList.add(3 + "-" + 1);
                    fontBoldList.add(4 + "-" + 1);
                    fontBoldList.add(5 + "-" + 1);

                    mergedRegion(sheet, 1, 1, 3, 4, detailData.getCodeOfProduct());
                    mergedRegion(sheet, 2, 2, 3, 4, detailData.getNameOfProduct());
                    mergedRegion(sheet, 3, 3, 3, 4,
                            Arrays.asList(detailData.getLengthOfProduct(), detailData.getWidthOfProduct(), detailData.getHeightOfProduct())
                                    .stream().map(Object::toString).collect(Collectors.joining("*")));
                    mergedRegion(sheet, 4, 4, 3, 4,
                            Objects.nonNull(detailData.getWeightOfProduct()) ? BigdecimalUtil.doubleTo3DecimalString(detailData.getWeightOfProduct()) : "");
                    mergedRegion(sheet, 5, 5, 3, 4, detailData.getMoldDimension());

                    mergedRegion(sheet, 1, 2, 5, 5, "模胚外形\n尺寸");
                    getRow(sheet, 1).createCell(6).setCellValue("长");
                    getRow(sheet, 1).createCell(7).setCellValue("宽");
                    getRow(sheet, 1).createCell(8).setCellValue("高");
                    getRow(sheet, 1).createCell(9).setCellValue("重量(Kg)");
                    getRow(sheet, 2).createCell(6).setCellValue(BigdecimalUtil.doubleTo2DecimalString(detailData.getLengthOfEmbryo()));
                    getRow(sheet, 2).createCell(7).setCellValue(BigdecimalUtil.doubleTo2DecimalString(detailData.getWidthOfEmbryo()));
                    getRow(sheet, 2).createCell(8).setCellValue(BigdecimalUtil.doubleTo2DecimalString(detailData.getHeightOfEmbryo()));
                    getRow(sheet, 2).createCell(9).setCellValue(BigdecimalUtil.doubleTo3DecimalString(detailData.getWeightOfEmbryo()));
                    fontBoldList.add(1 + "-" + 5);
                    fontBoldList.add(1 + "-" + 6);
                    fontBoldList.add(1 + "-" + 7);
                    fontBoldList.add(1 + "-" + 8);
                    fontBoldList.add(1 + "-" + 9);

                    mergedRegion(sheet, 3, 3, 5, 6, "零件材料");
                    mergedRegion(sheet, 4, 4, 5, 6, "表面要求");
                    mergedRegion(sheet, 5, 5, 5, 6, "模穴数");
                    mergedRegion(sheet, 3, 3, 7, 9, detailData.getMaterial());
                    mergedRegion(sheet, 4, 4, 7, 9, detailData.getSurfaceRequire());
                    mergedRegion(sheet, 5, 5, 7, 9, detailData.getCavityCount());
                    fontBoldList.add(3 + "-" + 5);
                    fontBoldList.add(4 + "-" + 5);
                    fontBoldList.add(5 + "-" + 5);

                    final String imageOfProduct = bydQuotationVo.getDataList().get(i).getImageOfProduct();
                    String imageUrl = "";
                    if (StringUtils.isNotEmpty(imageOfProduct)) {
                        imageUrl = rootUrl + File.separator + imageOfProduct;
                    }
                    cellImage(workbook, sheet, imageUrl, 10, 1, 11, 5);

                    //钢材成本
                    List<String> steelFeeTitleList = Arrays.asList("序号", "名目", "料号名称", "材料牌号", "数量", "长", "宽", "高", "重量(Kg)", "单价", "金额(元)", "小计(元)");
                    IntStream.range(0, steelFeeTitleList.size()).forEach(index -> {
                        getRow(sheet, 6).createCell(index).setCellValue(steelFeeTitleList.get(index));
                        fontBoldList.add(6 + "-" + index);
                    });
                    int row = 7;
                    if (Objects.nonNull(detailData.getSteelFee()) && Objects.nonNull(detailData.getSteelFee().getDataList())) {
                        List<SteelFeeVo.DataList> steelFeeDataList = detailData.getSteelFee().getDataList();
                        mergedRegion(sheet, row, row + steelFeeDataList.size() - 1, 0, 0, "1");
                        mergedRegion(sheet, row, row + steelFeeDataList.size() - 1, 1, 1, "钢材成本");
                        mergedRegion(sheet, row, row + steelFeeDataList.size() - 1, 11, 11, "￥" + BigdecimalUtil.doubleTo2DecimalString(detailData.getSteelFee().getSubTotal()));
                        fontBoldList.add(row + "-" + 0);
                        fontBoldList.add(row + "-" + 1);
                        for (int index = 0; index < steelFeeDataList.size(); index++) {
                            getRow(sheet, row).createCell(2).setCellValue(steelFeeDataList.get(index).getItemName());
                            getRow(sheet, row).createCell(3).setCellValue(steelFeeDataList.get(index).getSpecification());
                            getRow(sheet, row).createCell(4).setCellValue(steelFeeDataList.get(index).getCount());
                            getRow(sheet, row).createCell(5).setCellValue(BigdecimalUtil.doubleTo2DecimalString(steelFeeDataList.get(index).getLength()));
                            getRow(sheet, row).createCell(6).setCellValue(BigdecimalUtil.doubleTo2DecimalString(steelFeeDataList.get(index).getWidth()));
                            getRow(sheet, row).createCell(7).setCellValue(BigdecimalUtil.doubleTo2DecimalString(steelFeeDataList.get(index).getHeight()));
                            getRow(sheet, row).createCell(8).setCellValue(BigdecimalUtil.doubleTo3DecimalString(steelFeeDataList.get(index).getWeight()));
                            getRow(sheet, row).createCell(9).setCellValue(BigdecimalUtil.doubleTo2DecimalString(steelFeeDataList.get(index).getPrice()));
                            getRow(sheet, row).createCell(10).setCellValue(BigdecimalUtil.doubleTo2DecimalString(steelFeeDataList.get(index).getTotalPrice()));
                            row++;
                        }
                    }

                    //其他辅件
                    List<String> otherAccessoriesFeeTitleList = Arrays.asList("项目", "规格型号", "品牌", "数量", "单价", "金额(元)", "小计(元)");
                    for (int index = 0, col = 2; index < otherAccessoriesFeeTitleList.size(); index++, col++) {
                        String title = otherAccessoriesFeeTitleList.get(index);
                        if (title.equals("品牌") || title.equals("数量") || title.equals("单价")) {
                            mergedRegion(sheet, row, row, col, col + 1, title);
                            fontBoldList.add(row + "-" + col);
                            col++;
                        } else {
                            getRow(sheet, row).createCell(col).setCellValue(title);
                            fontBoldList.add(row + "-" + col);
                        }
                    }
                    row++;
                    if (Objects.nonNull(detailData.getOtherAccessoriesFee()) && Objects.nonNull(detailData.getOtherAccessoriesFee().getDataList())) {
                        List<OtherAccessoriesFeeVo.DataList> otherAccessoriesFeeVoDataList = detailData.getOtherAccessoriesFee().getDataList();
                        mergedRegion(sheet, row - 1, row + otherAccessoriesFeeVoDataList.size() - 1, 0, 0, "2");
                        mergedRegion(sheet, row - 1, row + otherAccessoriesFeeVoDataList.size() - 1, 1, 1, "其他辅件");
                        mergedRegion(sheet, row, row + otherAccessoriesFeeVoDataList.size() - 1, 11, 11, "￥" + BigdecimalUtil.doubleTo2DecimalString(detailData.getOtherAccessoriesFee().getSubTotal()));
                        fontBoldList.add(row - 1 + "-" + 0);
                        fontBoldList.add(row - 1 + "-" + 1);
                        for (int index = 0; index < otherAccessoriesFeeVoDataList.size(); index++) {
                            getRow(sheet, row).createCell(2).setCellValue(otherAccessoriesFeeVoDataList.get(index).getItemName());
                            getRow(sheet, row).createCell(3).setCellValue(otherAccessoriesFeeVoDataList.get(index).getSpecification());
                            mergedRegion(sheet, row, row, 4, 5, otherAccessoriesFeeVoDataList.get(index).getBrand());
                            mergedRegion(sheet, row, row, 6, 7, String.valueOf(otherAccessoriesFeeVoDataList.get(index).getCount()));
                            mergedRegion(sheet, row, row, 8, 9, BigdecimalUtil.doubleTo2DecimalString(otherAccessoriesFeeVoDataList.get(index).getPrice()));
                            getRow(sheet, row).createCell(10).setCellValue(BigdecimalUtil.doubleTo2DecimalString(otherAccessoriesFeeVoDataList.get(index).getTotalPrice()));
                            row++;
                        }
                    }

                    //制造成本
                    List<String> manufacturingFeeTitelList = Arrays.asList("项目", "工时(小时)", "单价", "金额(元)", "小计(元)");
                    for (int index = 0, col = 2; index < manufacturingFeeTitelList.size(); index++, col++) {
                        String title = manufacturingFeeTitelList.get(index);
                        if (title.equals("项目") || title.equals("单价")) {
                            mergedRegion(sheet, row, row, col, col + 1, title);
                            fontBoldList.add(row + "-" + col);
                            col++;
                        } else if (title.equals("工时(小时)")) {
                            mergedRegion(sheet, row, row, col, col + 3, title);
                            col = col + 3;
                        } else {
                            getRow(sheet, row).createCell(col).setCellValue(title);
                            fontBoldList.add(row + "-" + col);
                        }
                    }
                    row++;
                    if (Objects.nonNull(detailData.getManufacturingFee()) && Objects.nonNull(detailData.getManufacturingFee().getDataList())) {
                        List<ManufacturingFeeVo.DataList> manufacturingFeeList = detailData.getManufacturingFee().getDataList();
                        mergedRegion(sheet, row - 1, row + manufacturingFeeList.size() - 1, 0, 0, "3");
                        mergedRegion(sheet, row - 1, row + manufacturingFeeList.size() - 1, 1, 1, "制造成本");
                        mergedRegion(sheet, row, row + manufacturingFeeList.size() - 1, 11, 11, "￥" + BigdecimalUtil.doubleTo2DecimalString(detailData.getManufacturingFee().getSubTotal()));
                        fontBoldList.add(row - 1 + "-" + 0);
                        fontBoldList.add(row - 1 + "-" + 1);
                        for (int index = 0; index < manufacturingFeeList.size(); index++) {
                            mergedRegion(sheet, row, row, 2, 3, manufacturingFeeList.get(index).getItemName());
                            mergedRegion(sheet, row, row, 4, 7, BigdecimalUtil.doubleTo2DecimalString(manufacturingFeeList.get(index).getHour()));
                            mergedRegion(sheet, row, row, 8, 9, BigdecimalUtil.doubleTo2DecimalString(manufacturingFeeList.get(index).getPrice()));
                            getRow(sheet, row).createCell(10).setCellValue(BigdecimalUtil.doubleTo2DecimalString(manufacturingFeeList.get(index).getTotalPrice()));
                            row++;
                        }
                    }

                    //营业费用
                    List<String> operatingFeeTitelList = Arrays.asList("项目", "计算说明", "金额(元)", "小计(元)");
                    for (int index = 0, col = 2; index < operatingFeeTitelList.size(); index++, col++) {
                        String title = operatingFeeTitelList.get(index);
                        if (title.equals("计算说明")) {
                            mergedRegion(sheet, row, row, col, col + 6, title);
                            fontBoldList.add(row + "-" + col);
                            col = col + 6;
                        } else {
                            getRow(sheet, row).createCell(col).setCellValue(title);
                            fontBoldList.add(row + "-" + col);
                        }
                    }
                    row++;
                    if (Objects.nonNull(detailData.getOperatingFee()) && Objects.nonNull(detailData.getOperatingFee().getDataList())) {
                        List<OperatingFeeVo.DataList> operatingFeeList = detailData.getOperatingFee().getDataList();
                        mergedRegion(sheet, row - 1, row + operatingFeeList.size() - 1, 0, 0, "4");
                        mergedRegion(sheet, row - 1, row + operatingFeeList.size() - 1, 1, 1, "营业费用");
                        mergedRegion(sheet, row, row + operatingFeeList.size() - 1, 11, 11, "￥" + BigdecimalUtil.doubleTo2DecimalString(detailData.getOperatingFee().getSubTotal()));
                        fontBoldList.add(row - 1 + "-" + 0);
                        fontBoldList.add(row - 1 + "-" + 1);
                        for (int index = 0; index < operatingFeeList.size(); index++) {
                            getRow(sheet, row).createCell(2).setCellValue(operatingFeeList.get(index).getItemName());
                            mergedRegion(sheet, row, row, 3, 9, operatingFeeList.get(index).getDesc());
                            getRow(sheet, row).createCell(10).setCellValue(BigdecimalUtil.doubleTo2DecimalString(operatingFeeList.get(index).getTotalPrice()));
                            row++;
                        }
                    }

                    //总计
                    getRow(sheet, row).createCell(0).setCellValue("总计");
                    mergedRegion(sheet, row, row, 1, 2, "报价金额未税");
                    mergedRegion(sheet, row, row, 3, 11, Objects.nonNull(detailData.getOfferFee()) ? "￥" + BigdecimalUtil.doubleTo2DecimalString(detailData.getOfferFee()) : "￥0.00");
                    fontBoldList.add(row + "-" + 0);
                    fontBoldList.add(row + "-" + 1);
                    fontBoldList.add(row + "-" + 3);

                    // 设置整个表格的样式
                    sheet.forEach(sheetRow -> {
                        sheetRow.forEach(cell -> {
                            cell.setCellStyle(createCellStyle(workbook, false, 10));
                        });
                    });

                    //特殊样式
                    fontBoldList.forEach(x -> {
                        getRow(sheet, Integer.valueOf(x.split("-")[0])).getCell(Integer.valueOf(x.split("-")[1])).setCellStyle(createCellStyle(workbook, true, 10));
                    });
                    getRow(sheet, 0).getCell(0).setCellStyle(createCellStyle(workbook, true, 14));

                });

                FileOutputStream out = new FileOutputStream(filePath);
                workbook.write(out);
                out.flush();
                out.close();
                workbook.close();
            } else {
                log.info("BYD塑胶模具报价单导出时数据为空");
            }

        } catch (Exception e) {
            e.printStackTrace();
        }

        log.info("BYD塑胶模具报价单导出成功");
        return resultPath;
    }

    @Override
    public String exportBYDNewOutQuotation(String data) {
        String rootUrl = keyWord.getFileUrl();
        String resultPath = "";
        try {
            R r = JSON.parseObject(data, R.class);
            BYDQuotationVo bydQuotationVo = JSON.parseObject(JSON.toJSONString(r.getData()), BYDQuotationVo.class);
            String moldSelectionId = bydQuotationVo.getMoldSelectionId();
            String projectName = iMoldSelectionService.getProjectName(moldSelectionId);
            resultPath = "file" + File.separator + projectName + "项目-BYD塑胶模具报价单-" + DateUtils.dateTimeNow("yyyyMMdd-SSS") + ".xlsx";
            String filePath = rootUrl + File.separator + resultPath;
            if (Objects.nonNull(bydQuotationVo)) {
                //处理数据
                Map<String, Object> resultMap = new HashMap<>();
                resultMap.put("projectName", bydQuotationVo.getProjectName());
                resultMap.put("inspectionToolFeeTotal", BigdecimalUtil.doubleTo2DecimalString(bydQuotationVo.getInspectionToolFeeTotal()));
                resultMap.put("modlueFeeTotal", BigdecimalUtil.doubleTo2DecimalString(bydQuotationVo.getModlueFeeTotal()));
                BigDecimal samplePieceFeeTotal = bydQuotationVo.getDataList().stream().map(x -> BigDecimal.valueOf(x.getSamplePieceFee())).reduce(BigDecimal.ZERO, BigDecimal::add);
                resultMap.put("samplePieceFeeTotal", BigdecimalUtil.bigDecimalTo2DecimalString(samplePieceFeeTotal));

                List<Map<String, Object>> dataList = new ArrayList<>(bydQuotationVo.getDataList().size());
                List<Map<String, Object>> detailList = new ArrayList<>(bydQuotationVo.getDataList().size());
                if (Objects.nonNull(bydQuotationVo.getDataList()) && !bydQuotationVo.getDataList().isEmpty()) {
                    IntStream.range(0, bydQuotationVo.getDataList().size()).forEach(i -> {
                        BYDQuotationDetailVo bydQuotationDetailVo = bydQuotationVo.getDataList().get(i);
                        Map<String, Object> bydQuotationDetailVoMap = BeanUtil.beanToMap(bydQuotationDetailVo, false, false);
                        //序号
                        bydQuotationDetailVoMap.put("index", i + 1);
                        //图片
                        final String imageOfProduct = bydQuotationDetailVo.getImageOfProduct();
                        if (StringUtils.isNotEmpty(imageOfProduct)) {
                            bydQuotationDetailVoMap.put("imageFile", new File(rootUrl + File.separator + imageOfProduct));
                        }
                        bydQuotationDetailVoMap.put("samplePieceFee", BigdecimalUtil.doubleTo2DecimalString(bydQuotationDetailVo.getSamplePieceFee()));
                        bydQuotationDetailVoMap.put("inspectionToolFee", BigdecimalUtil.doubleTo2DecimalString(bydQuotationDetailVo.getInspectionToolFee()));
                        bydQuotationDetailVoMap.put("modlueFee", BigdecimalUtil.doubleTo2DecimalString(bydQuotationDetailVo.getModlueFee()));
                        dataList.add(bydQuotationDetailVoMap);
                        //样件单价
                        samplePieceFeeTotal.add(BigDecimal.valueOf(bydQuotationDetailVo.getSamplePieceFee())).setScale(2, RoundingMode.HALF_UP);
                    });

                    //报价明细
                    IntStream.range(0, bydQuotationVo.getDataList().size()).forEach(i -> {
                        Map<String, Object> map = new HashMap<>();
                        BYDQuotationSubDetailVo detailData = bydQuotationVo.getDataList().get(i).getDetailData();
                        //序号
                        map.put("index", i + 1);
                        map.put("codeOfProduct", detailData.getCodeOfProduct());
                        map.put("nameOfProduct", detailData.getNameOfProduct());
                        String sizeOfProduct = Arrays.asList(detailData.getLengthOfProduct(), detailData.getWidthOfProduct(), detailData.getHeightOfProduct())
                                .stream().map(Object::toString).collect(Collectors.joining("*"));
                        map.put("sizeOfProduct", sizeOfProduct);
                        map.put("offerFee", Objects.nonNull(detailData.getOfferFee()) ? BigdecimalUtil.doubleTo2DecimalString(detailData.getOfferFee()) : "0.00");

                        //设计费用
                        setDesignCostVo(detailData, map);
                        //材料费用明细
                        setSteelFeeVo(detailData, map);
                        //外购辅件
                        setOtherAccessoriesFeeVo(detailData, map);
                        //制造费用
                        setManufacturingFee(detailData, map);
                        //营业费用
                        setOperatingFee(detailData, map);

                        detailList.add(map);
                    });
                }


                try (ExcelWriter excelWriter = EasyExcel.write(filePath)
                        .inMemory(Boolean.TRUE)
                        .registerWriteHandler(EasyExcelUtil.getStyleStrategy())
                        .registerWriteHandler(new CustomTemplateSheetStrategy(0, bydQuotationVo.getProjectName() + "项目-总报价单"))
                        .withTemplate(getClass().getClassLoader().getResourceAsStream("templates/byd_quotation_template.xlsx"))
                        .build()) {
                    FillConfig fillConfig = FillConfig.builder().forceNewRow(Boolean.TRUE).build();
                    WriteSheet writeSheet0 = EasyExcel.writerSheet(0).build();
                    WriteSheet writeSheet1 = EasyExcel.writerSheet(1).build();
                    excelWriter.fill(dataList, fillConfig, writeSheet0);
                    excelWriter.fill(resultMap, writeSheet0);
                    excelWriter.fill(detailList, fillConfig, writeSheet1);
                    excelWriter.finish();
                }
            } else {
                log.info("BYD塑胶模具报价单导出时数据为空");
            }

        } catch (Exception e) {
            e.printStackTrace();
        }

        log.info("BYD塑胶模具报价单导出成功");
        return resultPath;
    }


    public void setManufacturingFee(BYDQuotationSubDetailVo detailData, Map<String, Object> map) {
        ManufacturingFeeVo manufacturingFee = detailData.getManufacturingFee();
        if (Objects.nonNull(manufacturingFee)) {
            map.put("manufacturingFee.subTotal", BigdecimalUtil.doubleTo2DecimalString(manufacturingFee.getSubTotal()));
            List<ManufacturingFeeVo.DataList> dataList = manufacturingFee.getDataList();

            // 调用方法处理不同的制造费用项
            handleManufacturingFee(dataList, map, "深孔钻", "skz");
            handleManufacturingFee(dataList, map, "CNC1(大机/高速机)钢件加工", "cnc1");
            handleManufacturingFee(dataList, map, "CNC2(中小)钢件加工", "cnc2");
            handleManufacturingFee(dataList, map, "CNC3(卧铣+五轴)", "cnc3");
            handleManufacturingFee(dataList, map, "CNC电极加工)", "cnc");
            handleManufacturingFee(dataList, map, "EDM1(大韩双头+阿奇镜面))", "edm1");
            handleManufacturingFee(dataList, map, "EDM2放电加工", "edm2");
            handleManufacturingFee(dataList, map, "WEDM快走丝加工", "wedmk");
            handleManufacturingFee(dataList, map, "WEDM慢走丝加工", "wedmm");
            handleManufacturingFee(dataList, map, "钳工装配加工费", "qg");
            handleManufacturingFee(dataList, map, "省模", "sm");
            handleManufacturingFee(dataList, map, "检测", "jc");
            handleManufacturingFee(dataList, map, "试模", "sm");

        }
    }

    // 定义一个方法用于处理制造费用
    private void handleManufacturingFee(List<ManufacturingFeeVo.DataList> dataList, Map<String, Object> map, String itemName, String itemNamePrefix) {

        Optional<ManufacturingFeeVo.DataList> optionalData = dataList.parallelStream()
                .filter(x -> x.getItemName().startsWith(itemName))
                .findFirst();
        if (optionalData.isPresent()) {
            ManufacturingFeeVo.DataList data = optionalData.get();
            map.put("manufacturingFee." + itemNamePrefix + ".price", BigdecimalUtil.doubleTo2DecimalString(data.getPrice()));
            map.put("manufacturingFee." + itemNamePrefix + ".hour", BigdecimalUtil.doubleTo2DecimalString(data.getHour()));
            map.put("manufacturingFee." + itemNamePrefix + ".totalPrice", BigdecimalUtil.doubleTo2DecimalString(data.getTotalPrice()));
            if (itemNamePrefix.equals("sm")) {
                map.put("manufacturingFee." + itemNamePrefix + ".supplier", BigdecimalUtil.doubleTo2DecimalString(data.getSupplier()));
            }
        } else {
            map.put("manufacturingFee." + itemNamePrefix + ".price", "0.00");
            map.put("manufacturingFee." + itemNamePrefix + ".hour", "0.00");
            map.put("manufacturingFee." + itemNamePrefix + ".totalPrice", "0.00");
            if (itemNamePrefix.equals("sm")) {
                map.put("manufacturingFee." + itemNamePrefix + ".supplier", "0.00");
            }
        }
    }


    public void setOperatingFee(BYDQuotationSubDetailVo detailData, Map<String, Object> map) {
        OperatingFeeVo operatingFee = detailData.getOperatingFee();

        map.put("operatingFee.subTotal", "0.00");

        map.put("operatingFee.bzf.totalPrice", "0.00");
        map.put("operatingFee.ysf.totalPrice", "0.00");

        map.put("operatingFee.glf.desc", "0%");
        map.put("operatingFee.glf.totalPrice", "0.00");

        map.put("operatingFee.lr.totalPrice", "0%");
        map.put("operatingFee.lr.totalPrice", "0.00");

        if (Objects.nonNull(operatingFee)) {
            map.put("operatingFee.subTotal", BigdecimalUtil.doubleTo2DecimalString(operatingFee.getSubTotal()));
            List<OperatingFeeVo.DataList> dataList = operatingFee.getDataList();
            List<OperatingFeeVo.DataList> bzf = dataList.stream().filter(x -> x.getItemName().equals("包装费")).collect(Collectors.toList());
            if (Objects.nonNull(bzf) && !bzf.isEmpty()) {
                map.put("operatingFee.bzf.totalPrice", BigdecimalUtil.doubleTo2DecimalString(bzf.get(0).getTotalPrice()));
            }
            List<OperatingFeeVo.DataList> ysf = dataList.stream().filter(x -> x.getItemName().equals("运输费")).collect(Collectors.toList());
            if (Objects.nonNull(ysf) && !ysf.isEmpty()) {
                map.put("operatingFee.ysf.totalPrice", BigdecimalUtil.doubleTo2DecimalString(ysf.get(0).getTotalPrice()));
            }
            List<OperatingFeeVo.DataList> glf = dataList.stream().filter(x -> x.getItemName().equals("管理费")).collect(Collectors.toList());
            if (Objects.nonNull(glf) && !glf.isEmpty()) {
                map.put("operatingFee.glf.desc", glf.get(0).getDesc());
                map.put("operatingFee.glf.totalPrice", BigdecimalUtil.doubleTo2DecimalString(glf.get(0).getTotalPrice()));
            }
            List<OperatingFeeVo.DataList> lr = dataList.stream().filter(x -> x.getItemName().equals("利润")).collect(Collectors.toList());
            if (Objects.nonNull(lr) && !lr.isEmpty()) {
                map.put("operatingFee.lr.desc", lr.get(0).getDesc());
                map.put("operatingFee.lr.totalPrice", BigdecimalUtil.doubleTo2DecimalString(lr.get(0).getTotalPrice()));
            }
        }
    }

    private void setOtherAccessoriesFeeVo(BYDQuotationSubDetailVo detailData, Map<String, Object> map) {
        OtherAccessoriesFeeVo otherAccessoriesFee = detailData.getOtherAccessoriesFee();

        if (Objects.nonNull(otherAccessoriesFee)) {
            map.put("otherAccessoriesFee.subTotal", BigdecimalUtil.doubleTo2DecimalString(otherAccessoriesFee.getSubTotal()));
            List<OtherAccessoriesFeeVo.DataList> otherAccessoriesFeeDataList = otherAccessoriesFee.getDataList();
            handleOtherAccessoriesFeeVo(otherAccessoriesFeeDataList, map, "热流道", "rld");
            handleOtherAccessoriesFeeVo(otherAccessoriesFeeDataList, map, "模内切", "mnq");
            handleOtherAccessoriesFeeVo(otherAccessoriesFeeDataList, map, "油缸", "yg");
            handleOtherAccessoriesFeeVo(otherAccessoriesFeeDataList, map, "其他", "other");
        }
    }

    // 定义一个方法用于处理外购辅件
    private void handleOtherAccessoriesFeeVo(List<OtherAccessoriesFeeVo.DataList> dataList, Map<String, Object> map, String itemName, String itemNamePrefix) {
        Optional<OtherAccessoriesFeeVo.DataList> optionalData = dataList.parallelStream()
                .filter(x -> x.getItemName().startsWith(itemName))
                .findFirst();
        if (optionalData.isPresent()) {
            OtherAccessoriesFeeVo.DataList data = optionalData.get();
            map.put("otherAccessoriesFee." + itemNamePrefix + ".price", BigdecimalUtil.doubleTo2DecimalString(data.getPrice()));
            map.put("otherAccessoriesFee." + itemNamePrefix + ".brand", data.getBrand());
            map.put("otherAccessoriesFee." + itemNamePrefix + ".count", data.getCount());
            map.put("otherAccessoriesFee." + itemNamePrefix + ".totalPrice", BigdecimalUtil.doubleTo2DecimalString(data.getTotalPrice()));
        } else {
            map.put("otherAccessoriesFee." + itemNamePrefix + ".price", "0.00");
            map.put("otherAccessoriesFee." + itemNamePrefix + ".brand", "0.00");
            map.put("otherAccessoriesFee." + itemNamePrefix + ".count", 0);
            map.put("otherAccessoriesFee." + itemNamePrefix + ".totalPrice", "0.00");
        }
    }

    private void setSteelFeeVo(BYDQuotationSubDetailVo detailData, Map<String, Object> map) {
        SteelFeeVo steelFee = detailData.getSteelFee();

        map.put("steelFee.subTotal", "0.00");

        map.put("steelFee.mj.specification", "");
        map.put("steelFee.mj.length", "0.00");
        map.put("steelFee.mj.width", "0.00");
        map.put("steelFee.mj.height", "0.00");
        map.put("steelFee.mj.weight", "0.00");
        map.put("steelFee.mj.price", "0.00");
        map.put("steelFee.mj.totalPrice", "0.00");

        map.put("steelFee.qm.specification", "");
        map.put("steelFee.qm.length", "0.00");
        map.put("steelFee.qm.width", "0.00");
        map.put("steelFee.qm.height", "0.00");
        map.put("steelFee.qm.weight", "0.00");
        map.put("steelFee.qm.price", "0.00");
        map.put("steelFee.qm.totalPrice", "0.00");

        map.put("steelFee.hm.specification", "");
        map.put("steelFee.hm.length", "0.00");
        map.put("steelFee.hm.width", "0.00");
        map.put("steelFee.hm.height", "0.00");
        map.put("steelFee.hm.weight", "0.00");
        map.put("steelFee.hm.price", "0.00");
        map.put("steelFee.hm.totalPrice", "0.00");

        map.put("steelFee.hk.specification", "");
        map.put("steelFee.hk.weight", "0.00");
        map.put("steelFee.hk.price", "0.00");
        map.put("steelFee.hk.totalPrice", "0.00");

        map.put("steelFee.xd.specification", "");
        map.put("steelFee.xd.weight", "0.00");
        map.put("steelFee.xd.price", "0.00");
        map.put("steelFee.xd.totalPrice", "0.00");

        map.put("steelFee.tg.totalPrice", "0.00");
        map.put("steelFee.sm.totalPrice", "0.00");

        map.put("steelFee.pwfy.hour", "0.00");
        map.put("steelFee.pwfy.price", "0.00");
        map.put("steelFee.pwfy.totalPrice", "0.00");


        if (Objects.nonNull(steelFee)) {
            map.put("steelFee.subTotal", BigdecimalUtil.doubleTo2DecimalString(steelFee.getSubTotal()));
            List<SteelFeeVo.DataList> dataList = steelFee.getDataList();
            List<SteelFeeVo.DataList> mj = dataList.stream().filter(x -> x.getItemName().equals("模架")).collect(Collectors.toList());
            if (Objects.nonNull(mj) && !mj.isEmpty()) {
                map.put("steelFee.mj.specification", mj.get(0).getSpecification());
                map.put("steelFee.mj.length", BigdecimalUtil.doubleTo2DecimalString(mj.get(0).getLength()));
                map.put("steelFee.mj.width", BigdecimalUtil.doubleTo2DecimalString(mj.get(0).getWidth()));
                map.put("steelFee.mj.height", BigdecimalUtil.doubleTo2DecimalString(mj.get(0).getHeight()));
                map.put("steelFee.mj.weight", BigdecimalUtil.doubleTo2DecimalString(mj.get(0).getWeight()));
                map.put("steelFee.mj.price", BigdecimalUtil.doubleTo2DecimalString(mj.get(0).getPrice()));
                map.put("steelFee.mj.totalPrice", BigdecimalUtil.doubleTo2DecimalString(mj.get(0).getTotalPrice()));
            }
            List<SteelFeeVo.DataList> qm = dataList.stream().filter(x -> x.getItemName().equals("前模")).collect(Collectors.toList());
            if (Objects.nonNull(qm) && !qm.isEmpty()) {
                map.put("steelFee.qm.specification", qm.get(0).getSpecification());
                map.put("steelFee.qm.length", BigdecimalUtil.doubleTo2DecimalString(qm.get(0).getLength()));
                map.put("steelFee.qm.width", BigdecimalUtil.doubleTo2DecimalString(qm.get(0).getWidth()));
                map.put("steelFee.qm.height", BigdecimalUtil.doubleTo2DecimalString(qm.get(0).getHeight()));
                map.put("steelFee.qm.weight", BigdecimalUtil.doubleTo2DecimalString(qm.get(0).getWeight()));
                map.put("steelFee.qm.price", BigdecimalUtil.doubleTo2DecimalString(qm.get(0).getPrice()));
                map.put("steelFee.qm.totalPrice", BigdecimalUtil.doubleTo2DecimalString(qm.get(0).getTotalPrice()));
            }
            List<SteelFeeVo.DataList> hm = dataList.stream().filter(x -> x.getItemName().equals("后模")).collect(Collectors.toList());
            if (Objects.nonNull(hm) && !hm.isEmpty()) {
                map.put("steelFee.hm.specification", hm.get(0).getSpecification());
                map.put("steelFee.hm.length", BigdecimalUtil.doubleTo2DecimalString(hm.get(0).getLength()));
                map.put("steelFee.hm.width", BigdecimalUtil.doubleTo2DecimalString(hm.get(0).getWidth()));
                map.put("steelFee.hm.height", BigdecimalUtil.doubleTo2DecimalString(hm.get(0).getHeight()));
                map.put("steelFee.hm.weight", BigdecimalUtil.doubleTo2DecimalString(hm.get(0).getWeight()));
                map.put("steelFee.hm.price", BigdecimalUtil.doubleTo2DecimalString(hm.get(0).getPrice()));
                map.put("steelFee.hm.totalPrice", BigdecimalUtil.doubleTo2DecimalString(hm.get(0).getTotalPrice()));
            }
            List<SteelFeeVo.DataList> hk = dataList.stream().filter(x -> x.getItemName().equals("滑块")).collect(Collectors.toList());
            if (Objects.nonNull(hk) && !hk.isEmpty()) {
                map.put("steelFee.hk.specification", hk.get(0).getSpecification());
                map.put("steelFee.hk.weight", BigdecimalUtil.doubleTo2DecimalString(hk.get(0).getWeight()));
                map.put("steelFee.hk.price", BigdecimalUtil.doubleTo2DecimalString(hk.get(0).getPrice()));
                map.put("steelFee.hk.totalPrice", BigdecimalUtil.doubleTo2DecimalString(hk.get(0).getTotalPrice()));
            }
            List<SteelFeeVo.DataList> xd = dataList.stream().filter(x -> x.getItemName().equals("斜顶")).collect(Collectors.toList());
            if (Objects.nonNull(xd) && !xd.isEmpty()) {
                map.put("steelFee.xd.specification", xd.get(0).getSpecification());
                map.put("steelFee.xd.weight", BigdecimalUtil.doubleTo2DecimalString(xd.get(0).getWeight()));
                map.put("steelFee.xd.price", BigdecimalUtil.doubleTo2DecimalString(xd.get(0).getPrice()));
                map.put("steelFee.xd.totalPrice", BigdecimalUtil.doubleTo2DecimalString(xd.get(0).getTotalPrice()));
            }
            List<SteelFeeVo.DataList> tg = dataList.stream().filter(x -> x.getItemName().equals("铜公")).collect(Collectors.toList());
            if (Objects.nonNull(tg) && !tg.isEmpty()) {
                map.put("steelFee.tg.totalPrice", BigdecimalUtil.doubleTo2DecimalString(tg.get(0).getTotalPrice()));
            }
            List<SteelFeeVo.DataList> sm = dataList.stream().filter(x -> x.getItemName().equals("石墨")).collect(Collectors.toList());
            if (Objects.nonNull(sm) && !sm.isEmpty()) {
                map.put("steelFee.sm.totalPrice", BigdecimalUtil.doubleTo2DecimalString(sm.get(0).getTotalPrice()));
            }
            List<SteelFeeVo.DataList> pwfy = dataList.stream().filter(x -> x.getItemName().equals("皮纹费用")).collect(Collectors.toList());
            if (Objects.nonNull(pwfy) && !pwfy.isEmpty()) {
                map.put("steelFee.pwfy.hour", BigdecimalUtil.doubleTo2DecimalString(pwfy.get(0).getHour()));
                map.put("steelFee.pwfy.price", BigdecimalUtil.doubleTo2DecimalString(pwfy.get(0).getPrice()));
                map.put("steelFee.pwfy.totalPrice", BigdecimalUtil.doubleTo2DecimalString(pwfy.get(0).getTotalPrice()));
            }
        }
    }

    private void setDesignCostVo(BYDQuotationSubDetailVo detailData, Map<String, Object> map) {
        DesignCostVo designCost = detailData.getDesignCost();

        map.put("designCost.subTotal", "0.00");
        map.put("designCost.gyfx.price", "0.00");
        map.put("designCost.gyfx.hour", "0.00");
        map.put("designCost.gyfx.totalPrice", "0.00");
        map.put("designCost.jgsj.price", "0.00");
        map.put("designCost.jgsj.hour", "0.00");
        map.put("designCost.jgsj.totalPrice", "0.00");
        map.put("designCost.bc.price", "0.00");
        map.put("designCost.bc.hour", "0.00");
        map.put("designCost.bc.totalPrice", "0.00");

        if (Objects.nonNull(designCost)) {
            map.put("designCost.subTotal", BigdecimalUtil.doubleTo2DecimalString(designCost.getSubTotal()));
            List<DesignCostVo.DataList> designCostDataList = designCost.getDataList();
            List<DesignCostVo.DataList> gyfx = designCostDataList.stream().filter(x -> x.getItemName().equals("工艺分析")).collect(Collectors.toList());
            if (Objects.nonNull(gyfx) && !gyfx.isEmpty()) {
                map.put("designCost.gyfx.price", BigdecimalUtil.doubleTo2DecimalString(gyfx.get(0).getPrice()));
                map.put("designCost.gyfx.hour", BigdecimalUtil.doubleTo2DecimalString(gyfx.get(0).getHour()));
                map.put("designCost.gyfx.totalPrice", BigdecimalUtil.doubleTo2DecimalString(gyfx.get(0).getTotalPrice()));
            }
            List<DesignCostVo.DataList> jgsj = designCostDataList.stream().filter(x -> x.getItemName().equals("结构设计")).collect(Collectors.toList());
            if (Objects.nonNull(jgsj) && !jgsj.isEmpty()) {
                map.put("designCost.jgsj.price", BigdecimalUtil.doubleTo2DecimalString(jgsj.get(0).getPrice()));
                map.put("designCost.jgsj.hour", BigdecimalUtil.doubleTo2DecimalString(jgsj.get(0).getHour()));
                map.put("designCost.jgsj.totalPrice", BigdecimalUtil.doubleTo2DecimalString(jgsj.get(0).getTotalPrice()));
            }
            List<DesignCostVo.DataList> bc = designCostDataList.stream().filter(x -> x.getItemName().equals("编程")).collect(Collectors.toList());
            if (Objects.nonNull(bc) && !bc.isEmpty()) {
                map.put("designCost.bc.price", BigdecimalUtil.doubleTo2DecimalString(bc.get(0).getPrice()));
                map.put("designCost.bc.hour", BigdecimalUtil.doubleTo2DecimalString(bc.get(0).getHour()));
                map.put("designCost.bc.totalPrice", BigdecimalUtil.doubleTo2DecimalString(bc.get(0).getTotalPrice()));
            }
        }
    }


    /**
     * 合并单元格
     *
     * @param sheet
     * @param firstRow
     * @param lastRow
     * @param firstCol
     * @param lastCol
     * @param cellValue
     */
    private void mergedRegion(Sheet sheet, int firstRow, int lastRow, int firstCol, int lastCol, String cellValue) {
        // 合并单元格
        CellRangeAddress region = new CellRangeAddress(firstRow, lastRow, firstCol, lastCol);
        sheet.addMergedRegion(region);
        //设置样式
        regionStyle(sheet, region);
        //赋值
        getRow(sheet, firstRow).createCell(firstCol).setCellValue(cellValue);
    }

    /**
     * 获取行
     *
     * @param sheet
     * @param firstRow
     * @return
     */
    private Row getRow(Sheet sheet, int firstRow) {
        return sheet.getRow(firstRow) == null ? sheet.createRow(firstRow) : sheet.getRow(firstRow);
    }

    /**
     * 设置区域样式
     *
     * @param sheet
     * @param cellAddresses
     */
    private void regionStyle(Sheet sheet, CellRangeAddress cellAddresses) {
// 设置合并区域的边框样式
        RegionUtil.setBorderTop(BorderStyle.THIN, cellAddresses, sheet);
        RegionUtil.setBorderBottom(BorderStyle.THIN, cellAddresses, sheet);
        RegionUtil.setBorderLeft(BorderStyle.THIN, cellAddresses, sheet);
        RegionUtil.setBorderRight(BorderStyle.THIN, cellAddresses, sheet);
    }

    //Excel单元格插入图片
    public void cellImage(Workbook wb, Sheet sheet, String imageUrl, int firstCol, int firstRow, int lastCol,
                          int lastRow) {
        // 创建合并单元格
        CellRangeAddress mergedRegion = new CellRangeAddress(firstRow, lastRow, firstCol, lastCol);
        regionStyle(sheet, mergedRegion);
        sheet.addMergedRegion(mergedRegion);

        if (StringUtils.isNotEmpty(imageUrl)) {
            try (InputStream is = new FileInputStream(imageUrl)) {
                // 读取图片
                byte[] bytes = IOUtils.toByteArray(is);
                int pictureIdx = wb.addPicture(bytes, Workbook.PICTURE_TYPE_PNG);

                // 创建图像
                Drawing drawing = sheet.createDrawingPatriarch();
                CreationHelper helper = wb.getCreationHelper();
                ClientAnchor anchor = helper.createClientAnchor();
                anchor.setCol1(mergedRegion.getFirstColumn());
                anchor.setRow1(mergedRegion.getFirstRow());
                anchor.setCol2(mergedRegion.getLastColumn());
                anchor.setRow2(mergedRegion.getLastRow());
                anchor.setDx1(0);
                anchor.setDy1(0);
                anchor.setDx2(0);
                anchor.setDy2(0);
                Picture pict = drawing.createPicture(anchor, pictureIdx);

                // 计算合并单元格的宽度和高度
                double mergedWidth = 0.0;
                for (int i = mergedRegion.getFirstColumn(); i <= mergedRegion.getLastColumn(); i++) {
                    mergedWidth += sheet.getColumnWidth(i) / 256.0 * 7.0;
                }

                double mergedHeight = 0.0;
                for (int i = mergedRegion.getFirstRow(); i <= mergedRegion.getLastRow(); i++) {
                    Row row = sheet.getRow(i);
                    if (row != null) {
                        mergedHeight += row.getHeightInPoints() / 72 * 96;
                    } else {
                        mergedHeight += sheet.getDefaultRowHeightInPoints() / 72 * 96;
                    }
                }

                // 获取图像的原始大小
                double imageWidth = pict.getImageDimension().getWidth();
                double imageHeight = pict.getImageDimension().getHeight();

                // 计算图像的新大小以适应合并单元格
                double ratio = Math.min(mergedWidth / imageWidth, mergedHeight / imageHeight);
                int newWidth = (int) (imageWidth * ratio);
                int newHeight = (int) (imageHeight * ratio);

                // 设置图像的新大小
                anchor.setDx2(Units.toEMU(newWidth));
                anchor.setDy2(Units.toEMU(newHeight));
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    }

    /**
     * 设置单元格样式
     */
    private XSSFCellStyle createCellStyle(XSSFWorkbook workbook, boolean isBold, int fontSize) {
        XSSFCellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        //加边框
        cellStyle.setBorderBottom(BorderStyle.THIN);//下边框
        cellStyle.setBorderLeft(BorderStyle.THIN);//左边框
        cellStyle.setBorderRight(BorderStyle.THIN);//右边框
        cellStyle.setBorderTop(BorderStyle.THIN); //上边框
        cellStyle.setFillForegroundColor((short) 9);
        cellStyle.setWrapText(true);
        XSSFFont font = workbook.createFont();
        //设置字体名称
        font.setFontName("宋体");
        //设置字号
        font.setFontHeightInPoints((short) fontSize);
        font.setBold(isBold);
        //设置字体颜色
        font.setColor(IndexedColors.BLACK1.index);
        cellStyle.setFont(font);

        return cellStyle;
    }
}
